﻿Imports System.Data
Imports System.Data.SqlClient

Public Class EntregasDAL

    Sub New()

    End Sub



    Function inserir(ByVal lotacao As Integer, ByVal dataEntrega As Date, ByVal dataRetorno As Date, ByVal status As String, ByVal almoco As Double, ByVal jantar As Double,
                     ByVal diversos As Double, ByVal descricaodiversos As String, ByVal hotel As Double, ByVal subtotalmotorista As Double, ByVal combustivel As Double, ByVal pedagio As Double,
                     ByVal conserto As Double, ByVal kmsaida As Integer, ByVal kmchegada As Integer, ByVal kmpercorrido As Integer, ByVal subtotalveiculo As Double) As Integer
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim codigo As Integer
        Try

            'Comando para inserção de dados no banco
            cmd.CommandText = "INSERT INTO entregas (lot_codigo, ent_data_entrega, ent_data_retorno," +
                            " ent_gasto_almoco, ent_gasto_jantar, ent_gasto_diversos, ent_gasto_hotel," +
                            " ent_desc_diversos, ent_subtotal_motorista, ent_despesa_combustivel,ent_despesa_pedagio," +
                            " ent_conserto, ent_km_saida, ent_km_chegada, ent_km_percorrido, ent_subtotal_veiculo, ent_status)" +
                            " VALUES (@lotacao, @dataEntrega, @dataRet,@almoco,@jantar,@diversos, @hotel," +
                            " @descricaoDiversos, @subTotalMotorista, @combustivel, @pedagio, @conserto, @kmsaida, @kmchegada," +
                            " @kmpercorrido, @subTotalVeiculo, @status)"

            'Parâmetros de entrada
            cmd.Parameters.Add(New SqlParameter("@lotacao", SqlDbType.Int)).Value = lotacao
            cmd.Parameters.Add(New SqlParameter("@dataEntrega", SqlDbType.Date)).Value = dataEntrega
            cmd.Parameters.Add(New SqlParameter("@dataRet", SqlDbType.Date)).Value = dataRetorno
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.VarChar)).Value = status
            cmd.Parameters.Add(New SqlParameter("@almoco", SqlDbType.Money)).Value = almoco
            cmd.Parameters.Add(New SqlParameter("@jantar", SqlDbType.Money)).Value = jantar
            cmd.Parameters.Add(New SqlParameter("@hotel", SqlDbType.Money)).Value = hotel
            cmd.Parameters.Add(New SqlParameter("@diversos", SqlDbType.Money)).Value = diversos
            cmd.Parameters.Add(New SqlParameter("@descricaoDiversos", SqlDbType.VarChar)).Value = descricaodiversos
            cmd.Parameters.Add(New SqlParameter("@subTotalMotorista", SqlDbType.Money)).Value = subtotalmotorista
            cmd.Parameters.Add(New SqlParameter("@conserto", SqlDbType.Money)).Value = conserto
            cmd.Parameters.Add(New SqlParameter("@combustivel", SqlDbType.Money)).Value = combustivel
            cmd.Parameters.Add(New SqlParameter("@kmsaida", SqlDbType.Int)).Value = kmsaida
            cmd.Parameters.Add(New SqlParameter("@kmchegada", SqlDbType.Int)).Value = kmchegada
            cmd.Parameters.Add(New SqlParameter("@kmpercorrido", SqlDbType.Int)).Value = kmpercorrido
            cmd.Parameters.Add(New SqlParameter("@pedagio", SqlDbType.Money)).Value = pedagio
            cmd.Parameters.Add(New SqlParameter("@subTotalVeiculo", SqlDbType.Money)).Value = subtotalveiculo


            'Execução da inserção com retorno do valor da chave primária
            codigo = objD.executacomandoAI(cmd, "entregas")

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return codigo
    End Function


    Function localizar(ByVal descricao As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "ultimo") Then
                cmd.CommandText = "SELECT ent_codigo = max(ent_codigo) FROM entregas "
            ElseIf (descricao = "primeiro") Then
                cmd.CommandText = "SELECT ent_codigo = min(ent_codigo) FROM entregas "

            Else
                'Comando para localizar vários registros no banco
                'cmd.CommandText = "SELECT * FROM entregas WHERE seg_nome like @descricao"

                'Parâmetro de entrada
                'cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao + "%"
            End If
            'Execução do comando
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Function localizarLotacao(ByVal dtinicio As Date, ByVal dtfinal As Date) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            'Comando para localizar vários registros no banco
            cmd.CommandText = "SELECT * FROM lotacao WHERE lot_data >= @dtinicio and lot_data <= @dtFim and lot_status = 'Aberto'"

            'Parâmetro de entrada
            cmd.Parameters.Add(New SqlParameter("@dtinicio", SqlDbType.Date)).Value = dtinicio
            cmd.Parameters.Add(New SqlParameter("@dtFim", SqlDbType.Date)).Value = dtfinal

            'Execução do comando
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function
    Function localizarEntrega(ByVal dtinicio As Date, ByVal dtfinal As Date) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            'Comando para localizar vários registros no banco
            cmd.CommandText = "SELECT * FROM vw_consulta_entrega WHERE  ent_data_retorno >= @dtinicio and  ent_data_retorno <= @dtFim"

            'Parâmetro de entrada
            cmd.Parameters.Add(New SqlParameter("@dtinicio", SqlDbType.Date)).Value = dtinicio
            cmd.Parameters.Add(New SqlParameter("@dtFim", SqlDbType.Date)).Value = dtfinal

            'Execução do comando
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function


    Function localizar(ByVal codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            'Comando para localizar vários registros no banco
            cmd.CommandText = "SELECT * FROM entregas WHERE ent_codigo = @codigo"

            'Parâmetro de entrada 
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo

            'Execução do comando
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Public Sub todosEntregue(ByVal lotacao As Integer, ByVal status As Integer)
        Try
            Dim con As New Dados
            Dim cmd As SqlCommand
            Try
                cmd = New SqlCommand
                cmd.CommandText = "Exec sp_Escala_status_lotacao @lotacao, @status"
                cmd.Parameters.Add(New SqlParameter("@lotacao", SqlDbType.Int)).Value = lotacao
                cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = status
                con.executacomando(cmd)
            Catch ex As Exception
                Throw New Exception("", ex)
            Finally
                cmd = Nothing
            End Try
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub cargaEntregue(ByVal Status As Integer, ByVal carga As Integer)
        Try
            Dim con As New Dados
            Dim cmd As SqlCommand
            Try
                cmd = New SqlCommand
                cmd.CommandText = "Exec sp_Escala_status_carga_lotacao @Status, @carga"
                cmd.Parameters.Add(New SqlParameter("@Status", SqlDbType.Int)).Value = status
                cmd.Parameters.Add(New SqlParameter("@carga", SqlDbType.Int)).Value = carga
                con.executacomando(cmd)
            Catch ex As Exception
                Throw New Exception("", ex)
            Finally
                cmd = Nothing
            End Try
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Function localiza(ByVal codigo As Integer) As DataTable
        Dim tab As DataTable
        Dim con As New Dados
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_escala_cargas where lot_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = con.retdadosTab(cmd)
        Catch ex As Exception
            Throw New Exception("", ex)
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function

    Sub atualizaEmTransito(ByVal codigo As Integer)
        Dim con As New Dados
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "Exec sp_escala_em_transito @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            con.executacomando(cmd)
        Catch ex As Exception
            Throw New Exception("", ex)
        Finally
            cmd = Nothing
        End Try
    End Sub

End Class
